Missouri’s childcare deserts in 2023, by Zip Code Tabulation Area
Methodology
In the scipt etl/2_get_scta-2017-2021-acs.R, I retrieved the 5-Year ACS population estimates by ZCTA, and summed the age groups under six to get total amount of children five and under by ZCTA
The number of children five or under by ZCTA is from here on “demand” and I use it to create a ratio with the “supply,” which is licensed capacity of facilities by zip code, provided by ChildCareAware
In etl/3_zip-zcta-crosswalk-ChildCareAware.R, I crosswalked the zip code level data from ChildCareAware to ZCTA level data so that it can be properly compared to ACS population estimates by ZCTA
Any ZCTA where the demand is three times more than the supply is determined a “desert,” or in other words, any ZCTA where the children-to-slot ratio is equal to or more than 3:1
Any ZCTA where the ratio is equal to or more than 10:1 is “extreme” and marked in darker red for now on the map
Total number of children five or under in childcare deserts across Missouri in 2023
171,048 children of five or under across Missouri live in zip codes areas that are childcare deserts. This is over one-third of children five or under.
Population in childcare deserts
Total population
Methodology
The number of children in deserts is the total amount of children in zctas with ratios greater than or equal to 3:1
This equals out to 171,048 in 227 zctas that are childcare deserts. In total, there are 442,254 children under 5 or under across the state
How did childcare deserts change during the pandemic?
From 2019 to September 2020, the number of childcare programs decreased by 30%, leaving 70,000 more children in childcare deserts than the year before. During 2020, the number of children in childcare deserts was the highest it has reached in the last several years.
The number of programs bounced back sharply by six months later though, and since 2021, the number of children in childcare deserts has steadily towards where it was pre-pandemic.
Number of children in childcare deserts by month and year
Number of childcare programs by month and year
Age breakouts of programs that recieved money for startup, expansion, or enhancement
Of programs that received CRRSA funding for startup, expansion, or enhancement, less than half as many slots were added for infant and toddlers as for preschool aged children.
Potential difference between capacity and real number of slots
Of programs that received CRRSA funding for startup, expansion, or enhancement, several programs marked their anticipated capacity at 10 or more children less than the number of slots they added.
Did pandemic relief funding go to childcare desert zips we’ve indentified?
Of the total of 114,681,667 dollars in the data we recieved, less than a fourth of it went to childcare deserts.
Methodology
In the script 4_clean-relief-data.R, I cleaned and parsed addresses for each application to connect it to a valid Missouri zip code. In a several instances, zip codes were typed in correctly, and I had to manually look up the address and correct the zip code. These instances are marked accordingly in the script.
I then concatenated all the relief data we received together, by program, amount and zip
Finally, I crosswalked the zip codes to ZCTAs so that I could then compare how much money went to ZCTAs labeled as a desert versus ones labeled as not a desert
Source Code
---title: "Findings: Disappearing Daycare"execute: output: false echo: falseformat: html: code-tools: true theme: cosmo toc: true---```{r}library(tidyverse)library(janitor)library(readxl)library(tidycensus)library(here)library(sf)library(lubridate)library(tmap)library(DT)# Cache the zip code tabulation data we want from the Census options(tigris_use_cache =TRUE)# Sets tmap to interactive modetmap_mode("view")```# Missouri's childcare deserts in 2023, by Zip Code Tabulation Area```{r}# LOAD DATA# Load clean and crosswalked data for all zctas from ChildCareAware survey# There are 475 unique zctas for 2023 in the datachildcare_aware <-read_csv(here("data", "processed", "ChildCareAware_cleaned_and_crosswalked.csv")) %>%mutate(zcta =as.character(zcta)) childcare_aware_2023 <- childcare_aware %>%filter(year ==2023)# Load all zctas not included in ChildCareAware survey # There are 1023 unique zctas in MO according to thise crosswalkmo_zctas <-read_excel(here("data", "raw", "ZIPCodetoZCTACrosswalk2021UDS.xlsx"), sheet =1) %>%clean_names() %>%filter(state =="MO") %>%distinct(zcta)# 5 year ACS estimates pf population five and under for all zctas in the countryacs <-read_csv(here("data", "processed", "pop_zcta_5acs.csv"))crrsa_startup_expansion <-read_excel(here("data","raw", "CRRSA Startup and Expansion with Paid.xlsx")) %>%clean_names()# Load all relief data with cleaned zips and years for when and where money was dispersed to relief_funding <-read_csv(here("data", "processed", "all_relief_cleaned.csv")) %>%mutate(zip =as.character(zip))# Zip code to zcta crosswalk file from UDS Mapper zip_zcta_crosswalk <-read_excel(here("data", "raw", "ZIPCodetoZCTACrosswalk2021UDS.xlsx"), sheet =1) %>%clean_names()# Join crosswalk with relief funding zips to that each zip matches a zcta to correspond to desert data# Success! All zips found a zctarelief_zcta <- relief_funding %>%inner_join(zip_zcta_crosswalk, by =c("zip"="zip_code"))# Add back in all zctas not included in survey so that we can map them all togethermap_df <- childcare_aware_2023 %>%right_join(mo_zctas, by ="zcta") %>%mutate(ratio = pop_5_and_under / licensed_capacity) %>%mutate(status =case_when(is.na(ratio) ~"non-desert", ratio <3~"non-desert", ratio >=3& ratio <10~"desert", ratio >=10~"extreme", TRUE~"test")) %>%mutate(status_num =case_when(is.na(ratio) ~0, ratio <3~0, ratio >=3& ratio <10~1, ratio >=10~2, TRUE~0)) %>%select(zcta, status, status_num, ratio)``````{r}# JOIN CROSSWAKLED DATA WITH GEOM FOR MAPPING # Now we can use zctas to get geoms for mapping # Get the 2017-2021 5-year ACS ZTCAs for the whole US so that we can join these with zip codes and get geoms for mappingzcta_geoms <-get_acs(geography ="zip code tabulation area", variables ="B19013_001",summary_var ="B01001_001", geometry =TRUE) %>%select(GEOID, geometry)# Join the survey and non-survey data with geoms from Census for mapping # This join to zcta geoms from ACS data drops 5 values, but anti join shows they are all non-deserts and "point" zctas for PO boxes map_df_sf <- map_df %>%inner_join(zcta_geoms, by =c("zcta"="GEOID")) %>%st_as_sf(sf_column_name ="geometry")``````{r}#| output: true# MAP DATA tm_shape(map_df_sf) +tm_polygons("status_num",palette ="Reds",legend.show =FALSE)```## Methodology - In the scipt `etl/2_get_scta-2017-2021-acs.R`, I retrieved the 5-Year ACS population estimates by ZCTA, and summed the age groups under six to get total amount of children five and under by ZCTA- The number of children five or under by ZCTA is from here on "demand" and I use it to create a ratio with the "supply," which is licensed capacity of facilities by zip code, provided by ChildCareAware- In `etl/3_zip-zcta-crosswalk-ChildCareAware.R`, I crosswalked the zip code level data from ChildCareAware to ZCTA level data so that it can be properly compared to ACS population estimates by ZCTA - Any ZCTA where the demand is three times more than the supply is determined a "desert," or in other words, any ZCTA where the children-to-slot ratio is equal to or more than 3:1- Any ZCTA where the ratio is equal to or more than 10:1 is "extreme" and marked in darker red for now on the map# Total number of children five or under in childcare deserts across Missouri in 2023171,048 children of five or under across Missouri live in zip codes areas that are childcare deserts. This is over one-third of children five or under. ```{r}deserts_2023 <- childcare_aware %>%select(zcta, year, month, licensed_capacity, pop_5_and_under) %>%filter(year =="2023") %>%mutate(ratio = pop_5_and_under / licensed_capacity) %>%filter(ratio >=3)# 171,048 in 227 zctas that are childcare desertssum(deserts_2023$pop_5_and_under)# Same five ZCTAs didn't join, but again, these are non-deserts PO boxes anywaymo_zctas_with_pop <- mo_zctas %>%left_join(acs, by =c("zcta"="GEOID")) %>%select(zcta, pop_5_and_under)# 442,254 children under 5 or under across the statesum(mo_zctas_with_pop$pop_5_and_under, na.rm=TRUE)```### Population in childcare deserts ```{r}#| output: true#| column: pagedatatable(deserts_2023, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```### Total population```{r}#| output: true#| column: pagedatatable(mo_zctas_with_pop, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```## Methodology- The number of children in deserts is the total amount of children in zctas with ratios greater than or equal to 3:1- This equals out to 171,048 in 227 zctas that are childcare deserts. In total, there are 442,254 children under 5 or under across the state# How did childcare deserts change during the pandemic?From 2019 to September 2020, the number of childcare programs decreased by 30%, leaving 70,000 more children in childcare deserts than the year before. During 2020, the number of children in childcare deserts was the highest it has reached in the last several years. The number of programs bounced back sharply by six months later though, and since 2021, the number of children in childcare deserts has steadily towards where it was pre-pandemic. ```{r}yearly_deserts <- childcare_aware %>%mutate(ratio = pop_5_and_under / licensed_capacity) %>%filter(ratio >=3) %>%group_by(year, month) %>%summarize(children_in_deserts =sum(pop_5_and_under))num_programs_yearly <- childcare_aware %>%group_by(year, month) %>%summarize(num_program =sum(childcare_programs))```## Number of children in childcare deserts by month and year```{r}#| output: true#| column: pagedatatable(yearly_deserts, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```## Number of childcare programs by month and year```{r}#| output: true#| column: pagedatatable(num_programs_yearly, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Age breakouts of programs that recieved money for startup, expansion, or enhancementOf programs that received CRRSA funding for startup, expansion, or enhancement, less than half as many slots were added for infant and toddlers as for preschool aged children. ```{r}age_breakouts <- crrsa_startup_expansion %>%select(number_of_infant_toddler_slots_adding, number_of_preschool_slots_adding, number_of_school_age_slots_adding) %>%mutate_all(~replace(., is.na(.), 0)) %>%pivot_longer(number_of_infant_toddler_slots_adding:number_of_school_age_slots_adding, names_to ="age_group", values_to ="number")%>%group_by(age_group) %>%summarize(number =sum(number))``````{r}#| output: true#| column: pagedatatable(age_breakouts, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Potential difference between capacity and real number of slotsOf programs that received CRRSA funding for startup, expansion, or enhancement, several programs marked their anticipated capacity at 10 or more children less than the number of slots they added. ```{r}real_capacity <- crrsa_startup_expansion %>%select(dvn, at_startup, number_of_infant_toddler_slots_adding, number_of_preschool_slots_adding, number_of_school_age_slots_adding, total_anticipated_capacity) %>%mutate(slots = number_of_infant_toddler_slots_adding + number_of_preschool_slots_adding + number_of_school_age_slots_adding) %>%filter(slots > total_anticipated_capacity) ``````{r}#| output: true#| column: pagedatatable(real_capacity, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Did pandemic relief funding go to childcare desert zips we've indentified?Of the total of 114,681,667 dollars in the data we recieved, less than a fourth of it went to childcare deserts. <div><script type="text/javascript"defersrc="https://datawrapper.dwcdn.net/vqeRc/embed.js?v=1"charset="utf-8"></script><noscript><imgsrc="https://datawrapper.dwcdn.net/vqeRc/full.png"alt=""/></noscript></div>```{r}# Filter the capacity deserts to data from 2021 and 2022, aggregate the bi-yearly data to yearly, and remove non-desert zctaschildcare_aware_2021_2022 <- childcare_aware %>%select(zcta, year, month, licensed_capacity, pop_5_and_under) %>%filter(year %in%c("2021", "2022")) %>%group_by(zcta, year) %>%summarize(ratio =sum(pop_5_and_under, na.rm =TRUE) /sum(licensed_capacity, na.rm =TRUE), pop_5_and_under =mean(pop_5_and_under)) %>%filter(ratio >=3) %>%mutate(zcta =as.character(zcta))# This join is for all funding together relief_to_desert_join <- relief_zcta %>%group_by(zcta, year) %>%summarize(total =sum(amount)) %>%left_join(childcare_aware_2021_2022, by =c("zcta", "year")) %>%mutate(status =case_when(is.na(ratio) ~"non-desert", TRUE~"desert"))``````{r}# The big reveal total_desert_relief <- relief_to_desert_join %>%group_by(status) %>%summarize(total =sum(total))``````{r}#| output: true#| column: pagedatatable(total_desert_relief, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))``````{r}# This join is for funding my source relief_to_desert_type_join <- relief_zcta %>%group_by(zcta, year, type) %>%summarize(total =sum(amount)) %>%left_join(childcare_aware_2021_2022, by =c("zcta", "year")) %>%mutate(status =case_when(is.na(ratio) ~"non-desert", TRUE~"desert")) %>%group_by(type, status) %>%summarize(total =sum(total)) %>%pivot_wider(names_from = status, values_from = total)``````{r}#| output: true#| column: pagedatatable(relief_to_desert_type_join, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```## Methodology- In the script `4_clean-relief-data.R`, I cleaned and parsed addresses for each application to connect it to a valid Missouri zip code. In a several instances, zip codes were typed in correctly, and I had to manually look up the address and correct the zip code. These instances are marked accordingly in the script. - I then concatenated all the relief data we received together, by program, amount and zip - Finally, I crosswalked the zip codes to ZCTAs so that I could then compare how much money went to ZCTAs labeled as a desert versus ones labeled as not a desert
